#!/bin/bash
#dws_to_ads.sh all/表名 [日期]
#1、判断参数是否传入
if [ $# -lt 1 ]; then
    echo "必须传入all/表名..."
    exit 1
fi

#2、判断日期是否传入
[ "$2" ] && date=$2 || date=$(date -d '-1 day' +%F)

# 定义执行 SQL 的函数
execute_sql() {
    local sql="$1"
    /opt/module/hive/bin/hive -e "use medical;set hive.exec.dynamic.partition.mode=nonstrict;$sql"
    if [ $? -ne 0 ]; then
        echo "执行 SQL 失败: $sql"
        exit 1
    fi
}

#3、根据表名匹配加载数据
case $1 in
    "all")
        # 1. 交易主题
        # 1.1 交易综合统计
        sql="
insert overwrite table ads_trade_stats
select dt,
       recent_days,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from ads_trade_stats
union
select '$date' dt,
       consul.recent_days,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from (select 1                        recent_days,
             sum(consultation_amount) consultation_amount,
             sum(consultation_count)  consultation_count
      from dws_trade_hospital_gender_age_group_consultation_1d
      where dt = '$date'
      union
      select recent_days,
             sum(if(recent_days = 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,
             sum(if(recent_days = 7, consultation_count_7d, consultation_count_30d))   consultation_count
      from dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days) consul
         left join
     (select 1                                recent_days,
             sum(consultation_pay_suc_amount) consultation_pay_suc_amount,
             sum(consultation_pay_suc_count)  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
      where dt = '$date'
      union
      select recent_days,
             sum(if(recent_days = 7, consultation_pay_suc_amount_7d,
                    consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,
             sum(if(recent_days = 7, consultation_pay_suc_count_7d,
                    consultation_pay_suc_count_30d))  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days) consul_pay_suc
     on consul.recent_days = consul_pay_suc.recent_days
         left join
     (select 1                        recent_days,
             sum(prescription_amount) prescription_amount,
             sum(prescription_count)  prescription_count
      from dws_trade_hospital_gender_age_group_prescription_1d
      where dt = '$date'
      union
      select recent_days,
             sum(if(recent_days = 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,
             sum(if(recent_days = 7, prescription_count_7d, prescription_count_30d))   prescription_count
      from dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days) prescription
     on consul.recent_days = prescription.recent_days
         left join
     (select 1                                recent_days,
             sum(prescription_pay_suc_amount) prescription_pay_suc_amount,
             sum(prescription_pay_suc_count)  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
      where dt = '$date'
      union
      select recent_days,
             sum(if(recent_days = 7, prescription_pay_suc_amount_7d,
                    prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,
             sum(if(recent_days = 7, prescription_pay_suc_count_7d,
                    prescription_pay_suc_count_30d))  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days) prescription_pay_suc
     on consul.recent_days = prescription_pay_suc.recent_days;
"
        execute_sql "$sql"

        # 1.2 各医院交易统计
        sql="
insert overwrite table ads_hospital_trade_stats
select dt,
       recent_days,
       hospital_id,
       hospital_name,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from ads_hospital_trade_stats
union
select '$date' dt,
       consul.recent_days,
       consul.hospital_id,
       consul.hospital_name,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from (select 1                        recent_days,
             hospital_id,
             hospital_name,
             sum(consultation_amount) consultation_amount,
             sum(consultation_count)  consultation_count
      from dws_trade_hospital_gender_age_group_consultation_1d
      where dt = '$date'
      group by hospital_id,
               hospital_name
      union
      select recent_days,
             hospital_id,
             hospital_name,
             sum(if(recent_days = 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,
             sum(if(recent_days = 7, consultation_count_7d, consultation_count_30d))   consultation_count
      from dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               hospital_id,
               hospital_name) consul
         left join
     (select 1                                recent_days,
             hospital_id,
             hospital_name,
             sum(consultation_pay_suc_amount) consultation_pay_suc_amount,
             sum(consultation_pay_suc_count)  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
      where dt = '$date'
      group by hospital_id,
               hospital_name
      union
      select recent_days,
             hospital_id,
             hospital_name,
             sum(if(recent_days = 7, consultation_pay_suc_amount_7d,
                    consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,
             sum(if(recent_days = 7, consultation_pay_suc_count_7d,
                    consultation_pay_suc_count_30d))  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               hospital_id,
               hospital_name) consul_pay_suc
     on consul.recent_days = consul_pay_suc.recent_days
         and consul.hospital_id = consul_pay_suc.hospital_id
         and consul.hospital_name = consul_pay_suc.hospital_name
         left join
     (select 1                        recent_days,
             hospital_id,
             hospital_name,
             sum(prescription_amount) prescription_amount,
             sum(prescription_count)  prescription_count
      from dws_trade_hospital_gender_age_group_prescription_1d
      where dt = '$date'
      group by hospital_id,
               hospital_name
      union
      select recent_days,
             hospital_id,
             hospital_name,
             sum(if(recent_days = 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,
             sum(if(recent_days = 7, prescription_count_7d, prescription_count_30d))   prescription_count
      from dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               hospital_id,
               hospital_name) prescription
     on consul.recent_days = prescription.recent_days
         and consul.hospital_id = prescription.hospital_id
         and consul.hospital_name = prescription.hospital_name
         left join
     (select 1                                recent_days,
             hospital_id,
             hospital_name,
             sum(prescription_pay_suc_amount) prescription_pay_suc_amount,
             sum(prescription_pay_suc_count)  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
      where dt = '$date'
      group by hospital_id,
               hospital_name
      union
      select recent_days,
             hospital_id,
             hospital_name,
             sum(if(recent_days = 7, prescription_pay_suc_amount_7d,
                    prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,
             sum(if(recent_days = 7, prescription_pay_suc_count_7d,
                    prescription_pay_suc_count_30d))  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               hospital_id,
               hospital_name) prescription_pay_suc
     on consul.recent_days = prescription_pay_suc.recent_days
         and consul.hospital_id = prescription_pay_suc.hospital_id
         and consul.hospital_name = prescription_pay_suc.hospital_name;
"
        execute_sql "$sql"

        # 1.3 各性别患者交易统计
        sql="
insert overwrite table ads_gender_trade_stats
select dt,
       recent_days,
       gender_code,
       gender,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from ads_gender_trade_stats
union
select '$date' dt,
       consul.recent_days,
       consul.gender_code,
       consul.gender,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from (select 1                        recent_days,
             gender_code,
             gender,
             sum(consultation_amount) consultation_amount,
             sum(consultation_count)  consultation_count
      from dws_trade_hospital_gender_age_group_consultation_1d
      where dt = '$date'
      group by gender_code,
               gender
      union
      select recent_days,
             gender_code,
             gender,
             sum(if(recent_days = 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,
             sum(if(recent_days = 7, consultation_count_7d, consultation_count_30d))   consultation_count
      from dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               gender_code,
               gender) consul
         left join
     (select 1                                recent_days,
             gender_code,
             gender,
             sum(consultation_pay_suc_amount) consultation_pay_suc_amount,
             sum(consultation_pay_suc_count)  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
      where dt = '$date'
      group by gender_code,
               gender
      union
      select recent_days,
             gender_code,
             gender,
             sum(if(recent_days = 7, consultation_pay_suc_amount_7d,
                    consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,
             sum(if(recent_days = 7, consultation_pay_suc_count_7d,
                    consultation_pay_suc_count_30d))  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               gender_code,
               gender) consul_pay_suc
     on consul.recent_days = consul_pay_suc.recent_days
         and consul.gender_code = consul_pay_suc.gender_code
         and consul.gender = consul_pay_suc.gender
         left join
     (select 1                        recent_days,
             gender_code,
             gender,
             sum(prescription_amount) prescription_amount,
             sum(prescription_count)  prescription_count
      from dws_trade_hospital_gender_age_group_prescription_1d
      where dt = '$date'
      group by gender_code,
               gender
      union
      select recent_days,
             gender_code,
             gender,
             sum(if(recent_days = 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,
             sum(if(recent_days = 7, prescription_count_7d, prescription_count_30d))   prescription_count
      from dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               gender_code,
               gender) prescription
     on consul.recent_days = prescription.recent_days
         and consul.gender_code = prescription.gender_code
         and consul.gender = prescription.gender
         left join
     (select 1                                recent_days,
             gender_code,
             gender,
             sum(prescription_pay_suc_amount) prescription_pay_suc_amount,
             sum(prescription_pay_suc_count)  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
      where dt = '$date'
      group by gender_code,
               gender
      union
      select recent_days,
             gender_code,
             gender,
             sum(if(recent_days = 7, prescription_pay_suc_amount_7d,
                    prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,
             sum(if(recent_days = 7, prescription_pay_suc_count_7d,
                    prescription_pay_suc_count_30d))  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               gender_code,
               gender) prescription_pay_suc
     on consul.recent_days = prescription_pay_suc.recent_days
         and consul.gender_code = prescription_pay_suc.gender_code
         and consul.gender = prescription_pay_suc.gender;
"
        execute_sql "$sql"

        # 1.4 各年龄段患者交易统计
        sql="
insert overwrite table ads_age_group_trade_stats
select dt,
       recent_days,
       age_group,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from ads_age_group_trade_stats
union
select '$date' dt,
       consul.recent_days,
       consul.age_group,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from (select 1                        recent_days,
             age_group,
             sum(consultation_amount) consultation_amount,
             sum(consultation_count)  consultation_count
      from dws_trade_hospital_gender_age_group_consultation_1d
      where dt = '$date'
      group by age_group
      union
      select recent_days,
             age_group,
             sum(if(recent_days = 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,
             sum(if(recent_days = 7, consultation_count_7d, consultation_count_30d))   consultation_count
      from dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               age_group) consul
         left join
     (select 1                                recent_days,
             age_group,
             sum(consultation_pay_suc_amount) consultation_pay_suc_amount,
             sum(consultation_pay_suc_count)  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
      where dt = '$date'
      group by age_group
      union
      select recent_days,
             age_group,
             sum(if(recent_days = 7, consultation_pay_suc_amount_7d,
                    consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,
             sum(if(recent_days = 7, consultation_pay_suc_count_7d,
                    consultation_pay_suc_count_30d))  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               age_group) consul_pay_suc
     on consul.recent_days = consul_pay_suc.recent_days
         and consul.age_group = consul_pay_suc.age_group
         left join
     (select 1                        recent_days,
             age_group,
             sum(prescription_amount) prescription_amount,
             sum(prescription_count)  prescription_count
      from dws_trade_hospital_gender_age_group_prescription_1d
      where dt = '$date'
      group by age_group
      union
      select recent_days,
             age_group,
             sum(if(recent_days = 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,
             sum(if(recent_days = 7, prescription_count_7d, prescription_count_30d))   prescription_count
      from dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               age_group) prescription
     on consul.recent_days = prescription.recent_days
         and consul.age_group = prescription.age_group
         left join
     (select 1                                recent_days,
             age_group,
             sum(prescription_pay_suc_amount) prescription_pay_suc_amount,
             sum(prescription_pay_suc_count)  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
      where dt = '$date'
      group by age_group
      union
      select recent_days,
             age_group,
             sum(if(recent_days = 7, prescription_pay_suc_amount_7d,
                    prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,
             sum(if(recent_days = 7, prescription_pay_suc_count_7d,
                    prescription_pay_suc_count_30d))  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               age_group) prescription_pay_suc
     on consul.recent_days = prescription_pay_suc.recent_days
         and consul.age_group = prescription_pay_suc.age_group;
"		 
		execute_sql "$sql"
		
		#2. 医生主题
		#2.1 医生变动统计
        sql="
insert overwrite table ads_doctor_change_stats
select dt,
       recent_days,
       new_doctor_count,
       activated_doctor_count,
       active_doctor_count
from ads_doctor_change_stats
union
select '$date' dt,
       new.recent_days,
       new_doctor_count,
       activated_doctor_count,
       active_doctor_count
from (select recent_days,
             count(*) new_doctor_count
      from dwd_doctor_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
      where dt >= date_add('$date', -recent_days + 1)
      group by recent_days) new
         left join
     (select recent_days,
             count(*) activated_doctor_count
      from dws_trade_doctor_consultation_td lateral view explode(array(1, 7, 30)) tmp as recent_days
      where dt = '$date'
        and first_consultation_dt >= date_add('$date', -recent_days + 1)
      group by recent_days) activated
     on new.recent_days = activated.recent_days
         left join
     (select 1        recent_days,
             count(*) active_doctor_count
      from dws_trade_doctor_consultation_1d
      where dt = '$date'
        and consultation_count >= 2
      union
      select recent_days,
             count(*) active_doctor_count
      from dws_trade_doctor_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
        and ((recent_days = 7 and consultation_count_7d >= 2)
          or (recent_days = 30 and consultation_count_30d >= 2))
      group by recent_days) active
     on new.recent_days = active.recent_days;
"
		execute_sql "$sql"
		
		#3. 用户主题
		#3.1 用户变动统计
        sql="
insert overwrite table ads_user_change_stats
select dt,
       recent_days,
       new_user_count,
       new_patient_count
from ads_user_change_stats
union
select '$date' dt,
       new_user.recent_days,
       new_user_count,
       new_patient_count
from (select recent_days,
             count(*) new_user_count
      from dwd_user_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
      where dt >= date_add('$date', -recent_days + 1)
      group by recent_days) new_user
         left join
     (select recent_days,
             count(*) new_patient_count
      from dwd_user_patient_add_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
      where dt >= date_add('$date', -recent_days + 1)
      group by recent_days) new_patient
     on new_user.recent_days = new_patient.recent_days;
		"
		execute_sql "$sql"
		
		#4. 评价主题
		#4.1 评价综合统计
        sql="
insert overwrite table ads_review_stats
select dt,
       review_user_count,
       review_count,
       good_review_rate
from ads_review_stats
union
select '$date' dt,
       review_user_count,
       review_count,
       good_review_rate
from (select count(distinct user_id) review_user_count
      from dws_interaction_hospital_user_review_td
      where dt = '$date') user_count
         left join
     (select sum(review_count)                          review_count,
             sum(good_review_count) / sum(review_count) good_review_rate
      from dws_interaction_hospital_review_td
      where dt = '$date') review_stats;
		"
		execute_sql "$sql"
		
		#4.2 各医院评价统计
        sql="
insert overwrite table ads_hospital_review_stats
select dt,
       hospital_id,
       hospital_name,
       review_user_count,
       review_count,
       good_review_rate
from ads_hospital_review_stats
union
select '$date' dt,
       user_count.hospital_id,
       user_count.hospital_name,
       review_user_count,
       review_count,
       good_review_rate
from (select hospital_id,
             hospital_name,
             count(user_id) review_user_count
      from dws_interaction_hospital_user_review_td
      where dt = '$date'
      group by hospital_id,
               hospital_name) user_count
         left join
     (select hospital_id,
             hospital_name,
             review_count,
             good_review_count / review_count good_review_rate
      from dws_interaction_hospital_review_td
      where dt = '$date') review_stats
     on user_count.hospital_id = review_stats.hospital_id
         and user_count.hospital_name = review_stats.hospital_name;
		"
		execute_sql "$sql"
		;;
"ads_trade_stats")
sql="
insert overwrite table ads_trade_stats
select dt,
       recent_days,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from ads_trade_stats
union
select '$date' dt,
       consul.recent_days,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from (select 1                        recent_days,
             sum(consultation_amount) consultation_amount,
             sum(consultation_count)  consultation_count
      from dws_trade_hospital_gender_age_group_consultation_1d
      where dt = '$date'
      union
      select recent_days,
             sum(if(recent_days = 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,
             sum(if(recent_days = 7, consultation_count_7d, consultation_count_30d))   consultation_count
      from dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days) consul
         left join
     (select 1                                recent_days,
             sum(consultation_pay_suc_amount) consultation_pay_suc_amount,
             sum(consultation_pay_suc_count)  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
      where dt = '$date'
      union
      select recent_days,
             sum(if(recent_days = 7, consultation_pay_suc_amount_7d,
                    consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,
             sum(if(recent_days = 7, consultation_pay_suc_count_7d,
                    consultation_pay_suc_count_30d))  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days) consul_pay_suc
     on consul.recent_days = consul_pay_suc.recent_days
         left join
     (select 1                        recent_days,
             sum(prescription_amount) prescription_amount,
             sum(prescription_count)  prescription_count
      from dws_trade_hospital_gender_age_group_prescription_1d
      where dt = '$date'
      union
      select recent_days,
             sum(if(recent_days = 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,
             sum(if(recent_days = 7, prescription_count_7d, prescription_count_30d))   prescription_count
      from dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days) prescription
     on consul.recent_days = prescription.recent_days
         left join
     (select 1                                recent_days,
             sum(prescription_pay_suc_amount) prescription_pay_suc_amount,
             sum(prescription_pay_suc_count)  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
      where dt = '$date'
      union
      select recent_days,
             sum(if(recent_days = 7, prescription_pay_suc_amount_7d,
                    prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,
             sum(if(recent_days = 7, prescription_pay_suc_count_7d,
                    prescription_pay_suc_count_30d))  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days) prescription_pay_suc
     on consul.recent_days = prescription_pay_suc.recent_days;
"
        execute_sql "$sql"
;;
"ads_hospital_trade_stats")
sql="
insert overwrite table ads_hospital_trade_stats
select dt,
       recent_days,
       hospital_id,
       hospital_name,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from ads_hospital_trade_stats
union
select '$date' dt,
       consul.recent_days,
       consul.hospital_id,
       consul.hospital_name,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from (select 1                        recent_days,
             hospital_id,
             hospital_name,
             sum(consultation_amount) consultation_amount,
             sum(consultation_count)  consultation_count
      from dws_trade_hospital_gender_age_group_consultation_1d
      where dt = '$date'
      group by hospital_id,
               hospital_name
      union
      select recent_days,
             hospital_id,
             hospital_name,
             sum(if(recent_days = 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,
             sum(if(recent_days = 7, consultation_count_7d, consultation_count_30d))   consultation_count
      from dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               hospital_id,
               hospital_name) consul
         left join
     (select 1                                recent_days,
             hospital_id,
             hospital_name,
             sum(consultation_pay_suc_amount) consultation_pay_suc_amount,
             sum(consultation_pay_suc_count)  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
      where dt = '$date'
      group by hospital_id,
               hospital_name
      union
      select recent_days,
             hospital_id,
             hospital_name,
             sum(if(recent_days = 7, consultation_pay_suc_amount_7d,
                    consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,
             sum(if(recent_days = 7, consultation_pay_suc_count_7d,
                    consultation_pay_suc_count_30d))  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               hospital_id,
               hospital_name) consul_pay_suc
     on consul.recent_days = consul_pay_suc.recent_days
         and consul.hospital_id = consul_pay_suc.hospital_id
         and consul.hospital_name = consul_pay_suc.hospital_name
         left join
     (select 1                        recent_days,
             hospital_id,
             hospital_name,
             sum(prescription_amount) prescription_amount,
             sum(prescription_count)  prescription_count
      from dws_trade_hospital_gender_age_group_prescription_1d
      where dt = '$date'
      group by hospital_id,
               hospital_name
      union
      select recent_days,
             hospital_id,
             hospital_name,
             sum(if(recent_days = 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,
             sum(if(recent_days = 7, prescription_count_7d, prescription_count_30d))   prescription_count
      from dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               hospital_id,
               hospital_name) prescription
     on consul.recent_days = prescription.recent_days
         and consul.hospital_id = prescription.hospital_id
         and consul.hospital_name = prescription.hospital_name
         left join
     (select 1                                recent_days,
             hospital_id,
             hospital_name,
             sum(prescription_pay_suc_amount) prescription_pay_suc_amount,
             sum(prescription_pay_suc_count)  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
      where dt = '$date'
      group by hospital_id,
               hospital_name
      union
      select recent_days,
             hospital_id,
             hospital_name,
             sum(if(recent_days = 7, prescription_pay_suc_amount_7d,
                    prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,
             sum(if(recent_days = 7, prescription_pay_suc_count_7d,
                    prescription_pay_suc_count_30d))  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               hospital_id,
               hospital_name) prescription_pay_suc
     on consul.recent_days = prescription_pay_suc.recent_days
         and consul.hospital_id = prescription_pay_suc.hospital_id
         and consul.hospital_name = prescription_pay_suc.hospital_name;
"
        execute_sql "$sql"
;;
"ads_gender_trade_stats")
sql="
insert overwrite table ads_gender_trade_stats
select dt,
       recent_days,
       gender_code,
       gender,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from ads_gender_trade_stats
union
select '$date' dt,
       consul.recent_days,
       consul.gender_code,
       consul.gender,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from (select 1                        recent_days,
             gender_code,
             gender,
             sum(consultation_amount) consultation_amount,
             sum(consultation_count)  consultation_count
      from dws_trade_hospital_gender_age_group_consultation_1d
      where dt = '$date'
      group by gender_code,
               gender
      union
      select recent_days,
             gender_code,
             gender,
             sum(if(recent_days = 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,
             sum(if(recent_days = 7, consultation_count_7d, consultation_count_30d))   consultation_count
      from dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               gender_code,
               gender) consul
         left join
     (select 1                                recent_days,
             gender_code,
             gender,
             sum(consultation_pay_suc_amount) consultation_pay_suc_amount,
             sum(consultation_pay_suc_count)  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
      where dt = '$date'
      group by gender_code,
               gender
      union
      select recent_days,
             gender_code,
             gender,
             sum(if(recent_days = 7, consultation_pay_suc_amount_7d,
                    consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,
             sum(if(recent_days = 7, consultation_pay_suc_count_7d,
                    consultation_pay_suc_count_30d))  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               gender_code,
               gender) consul_pay_suc
     on consul.recent_days = consul_pay_suc.recent_days
         and consul.gender_code = consul_pay_suc.gender_code
         and consul.gender = consul_pay_suc.gender
         left join
     (select 1                        recent_days,
             gender_code,
             gender,
             sum(prescription_amount) prescription_amount,
             sum(prescription_count)  prescription_count
      from dws_trade_hospital_gender_age_group_prescription_1d
      where dt = '$date'
      group by gender_code,
               gender
      union
      select recent_days,
             gender_code,
             gender,
             sum(if(recent_days = 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,
             sum(if(recent_days = 7, prescription_count_7d, prescription_count_30d))   prescription_count
      from dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               gender_code,
               gender) prescription
     on consul.recent_days = prescription.recent_days
         and consul.gender_code = prescription.gender_code
         and consul.gender = prescription.gender
         left join
     (select 1                                recent_days,
             gender_code,
             gender,
             sum(prescription_pay_suc_amount) prescription_pay_suc_amount,
             sum(prescription_pay_suc_count)  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
      where dt = '$date'
      group by gender_code,
               gender
      union
      select recent_days,
             gender_code,
             gender,
             sum(if(recent_days = 7, prescription_pay_suc_amount_7d,
                    prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,
             sum(if(recent_days = 7, prescription_pay_suc_count_7d,
                    prescription_pay_suc_count_30d))  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               gender_code,
               gender) prescription_pay_suc
     on consul.recent_days = prescription_pay_suc.recent_days
         and consul.gender_code = prescription_pay_suc.gender_code
         and consul.gender = prescription_pay_suc.gender;
"
        execute_sql "$sql"
;;
"ads_age_group_trade_stats")
sql="
insert overwrite table ads_age_group_trade_stats
select dt,
       recent_days,
       age_group,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from ads_age_group_trade_stats
union
select '$date' dt,
       consul.recent_days,
       consul.age_group,
       consultation_amount,
       consultation_count,
       consultation_pay_suc_amount,
       consultation_pay_suc_count,
       prescription_amount,
       prescription_count,
       prescription_pay_suc_amount,
       prescription_pay_suc_count
from (select 1                        recent_days,
             age_group,
             sum(consultation_amount) consultation_amount,
             sum(consultation_count)  consultation_count
      from dws_trade_hospital_gender_age_group_consultation_1d
      where dt = '$date'
      group by age_group
      union
      select recent_days,
             age_group,
             sum(if(recent_days = 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,
             sum(if(recent_days = 7, consultation_count_7d, consultation_count_30d))   consultation_count
      from dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               age_group) consul
         left join
     (select 1                                recent_days,
             age_group,
             sum(consultation_pay_suc_amount) consultation_pay_suc_amount,
             sum(consultation_pay_suc_count)  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
      where dt = '$date'
      group by age_group
      union
      select recent_days,
             age_group,
             sum(if(recent_days = 7, consultation_pay_suc_amount_7d,
                    consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,
             sum(if(recent_days = 7, consultation_pay_suc_count_7d,
                    consultation_pay_suc_count_30d))  consultation_pay_suc_count
      from dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               age_group) consul_pay_suc
     on consul.recent_days = consul_pay_suc.recent_days
         and consul.age_group = consul_pay_suc.age_group
         left join
     (select 1                        recent_days,
             age_group,
             sum(prescription_amount) prescription_amount,
             sum(prescription_count)  prescription_count
      from dws_trade_hospital_gender_age_group_prescription_1d
      where dt = '$date'
      group by age_group
      union
      select recent_days,
             age_group,
             sum(if(recent_days = 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,
             sum(if(recent_days = 7, prescription_count_7d, prescription_count_30d))   prescription_count
      from dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               age_group) prescription
     on consul.recent_days = prescription.recent_days
         and consul.age_group = prescription.age_group
         left join
     (select 1                                recent_days,
             age_group,
             sum(prescription_pay_suc_amount) prescription_pay_suc_amount,
             sum(prescription_pay_suc_count)  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
      where dt = '$date'
      group by age_group
      union
      select recent_days,
             age_group,
             sum(if(recent_days = 7, prescription_pay_suc_amount_7d,
                    prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,
             sum(if(recent_days = 7, prescription_pay_suc_count_7d,
                    prescription_pay_suc_count_30d))  prescription_pay_suc_count
      from dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
      group by recent_days,
               age_group) prescription_pay_suc
     on consul.recent_days = prescription_pay_suc.recent_days
         and consul.age_group = prescription_pay_suc.age_group;
"		 
		execute_sql "$sql"
;;
"ads_doctor_change_stats")
sql="
insert overwrite table ads_doctor_change_stats
select dt,
       recent_days,
       new_doctor_count,
       activated_doctor_count,
       active_doctor_count
from ads_doctor_change_stats
union
select '$date' dt,
       new.recent_days,
       new_doctor_count,
       activated_doctor_count,
       active_doctor_count
from (select recent_days,
             count(*) new_doctor_count
      from dwd_doctor_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
      where dt >= date_add('$date', -recent_days + 1)
      group by recent_days) new
         left join
     (select recent_days,
             count(*) activated_doctor_count
      from dws_trade_doctor_consultation_td lateral view explode(array(1, 7, 30)) tmp as recent_days
      where dt = '$date'
        and first_consultation_dt >= date_add('$date', -recent_days + 1)
      group by recent_days) activated
     on new.recent_days = activated.recent_days
         left join
     (select 1        recent_days,
             count(*) active_doctor_count
      from dws_trade_doctor_consultation_1d
      where dt = '$date'
        and consultation_count >= 2
      union
      select recent_days,
             count(*) active_doctor_count
      from dws_trade_doctor_consultation_nd lateral view explode(array(7, 30)) tmp as recent_days
      where dt = '$date'
        and ((recent_days = 7 and consultation_count_7d >= 2)
          or (recent_days = 30 and consultation_count_30d >= 2))
      group by recent_days) active
     on new.recent_days = active.recent_days;
"
		execute_sql "$sql"
;;
"ads_user_change_stats")
sql="
insert overwrite table ads_user_change_stats
select dt,
       recent_days,
       new_user_count,
       new_patient_count
from ads_user_change_stats
union
select '$date' dt,
       new_user.recent_days,
       new_user_count,
       new_patient_count
from (select recent_days,
             count(*) new_user_count
      from dwd_user_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
      where dt >= date_add('$date', -recent_days + 1)
      group by recent_days) new_user
         left join
     (select recent_days,
             count(*) new_patient_count
      from dwd_user_patient_add_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
      where dt >= date_add('$date', -recent_days + 1)
      group by recent_days) new_patient
     on new_user.recent_days = new_patient.recent_days;
		"
		execute_sql "$sql"
;;
"ads_review_stats")
sql="
insert overwrite table ads_review_stats
select dt,
       review_user_count,
       review_count,
       good_review_rate
from ads_review_stats
union
select '$date' dt,
       review_user_count,
       review_count,
       good_review_rate
from (select count(distinct user_id) review_user_count
      from dws_interaction_hospital_user_review_td
      where dt = '$date') user_count
         left join
     (select sum(review_count)                          review_count,
             sum(good_review_count) / sum(review_count) good_review_rate
      from dws_interaction_hospital_review_td
      where dt = '$date') review_stats;
		"
		execute_sql "$sql"
;;
"ads_hospital_review_stats")
sql="
insert overwrite table ads_hospital_review_stats
select dt,
       hospital_id,
       hospital_name,
       review_user_count,
       review_count,
       good_review_rate
from ads_hospital_review_stats
union
select '$date' dt,
       user_count.hospital_id,
       user_count.hospital_name,
       review_user_count,
       review_count,
       good_review_rate
from (select hospital_id,
             hospital_name,
             count(user_id) review_user_count
      from dws_interaction_hospital_user_review_td
      where dt = '$date'
      group by hospital_id,
               hospital_name) user_count
         left join
     (select hospital_id,
             hospital_name,
             review_count,
             good_review_count / review_count good_review_rate
      from dws_interaction_hospital_review_td
      where dt = '$date') review_stats
     on user_count.hospital_id = review_stats.hospital_id
         and user_count.hospital_name = review_stats.hospital_name;
		"
		execute_sql "$sql"
;;
*)
	echo "表名输入错误..."
	exit 1
;;
esac

echo "数据统计加载完成"
		